﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Corsat.Library.DataManager
{
    public static partial class Main
    {
        public static DataTable GetAbsence(int CourseId, int Year)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetCourseAbsence.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            command.Parameters.Add("@CourseId", SqlDbType.Int).Value = CourseId;
            command.Parameters.Add("@Year", SqlDbType.Int).Value = Year;

            #endregion

            #region Get Data

            try
            {
                DataTable dtAbsence = new DataTable();
                adapter.SelectCommand = command;
                adapter.Fill(dtAbsence);

                if (dtAbsence != null && dtAbsence.Rows.Count > 0)
                    return dtAbsence;
                else
                    return null;
            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }

        public static bool SaveAbsence(int CourseId, DataTable dtAbsence)
        {

            StringBuilder UpdateStatement = new StringBuilder();

            if (dtAbsence.Rows.Count > 0)
            {
                foreach (DataRow drAccount in dtAbsence.Rows)
                {
                    UpdateStatement.Append(@" 
                        EXEC sp_UpdateCourseAbsence "
                        + drAccount["AccountId"]
                        + "," + CourseId
                        + "," + DateTime.Now.Year.ToString()
                        + "," + Convert.ToInt32(drAccount["Att_1"]) 
                        + "," + Convert.ToInt32(drAccount["Att_2"]) 
                        + "," + Convert.ToInt32(drAccount["Att_3"]) 
                        + "," + Convert.ToInt32(drAccount["Att_4"]) 
                        + "," + Convert.ToInt32(drAccount["Att_5"]) 
                        + "," + Convert.ToInt32(drAccount["Att_6"]) 
                        + "," + Convert.ToInt32(drAccount["Att_7"]) 
                        + ";");
                }

                command.CommandText = UpdateStatement.ToString();
                command.CommandType = CommandType.Text;
                int rowsAffected = 0;

                try
                {
                    OpenConnection();
                    rowsAffected = command.ExecuteNonQuery();
                    CloseConnection();
                    return true;
                }
                catch (Exception exc)
                {
                    CloseConnection();
                    return false;
                }


            }
            return false;
        }
    }
}
